import os
import pymysql
import numpy as np
import pandas as pd
from sqlalchemy import create_engine


d = os.path.dirname('.')
py_dir = os.path.abspath(d)
os.chdir(py_dir)


# 连接MySQL数据库方式
def connect_method(method='python'):
    if method == 'python':  # Python连接
        conn = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            password='12345',
            database='zy_recommend',
            charset='utf8'
        )
        return conn
    elif method == 'pandas':  # pandas连接
        conn = create_engine('mysql+pymysql://root:12345@localhost:3306/zy_recommend?charset=utf8')
        return conn
    else:
        raise TypeError('method无效')


# 创建表
def creat_table(conn):
    if isinstance(conn, pymysql.connections.Connection):
        # sql创建表语句
        sql_status = 'create table status(' \
                     'id int auto_increment primary key,' \
                     'content varchar(20) null)'
        sql_category = 'create table category(' \
                       'id int primary key auto_increment,' \
                       'content varchar(100) not null);'
        sql_customer = 'create table customer (' \
                       'id int primary key auto_increment,' \
                       'tel varchar(32),' \
                       'category_id int,' \
                       'constraint c1 foreign key (category_id) references category(id) on delete cascade);'
        sql_salesstaff = 'create table salesstaff(' \
                         'id int primary key auto_increment,' \
                         'name varchar(16),' \
                         'account varchar(16) not null unique ,' \
                         'password varchar(32) not null );'
        sql_mission = 'create table mission(' \
                      'id int primary key auto_increment,' \
                      'customer_id int not null ,' \
                      'salesstaff_id int not null,' \
                      'createDate date not null,' \
                      'status_id int not null,' \
                      'constraint c2 foreign key (customer_id) references customer(id) on delete cascade ,' \
                      'constraint c3 foreign key (salesstaff_id) references salesstaff(id) on delete cascade ,' \
                      'constraint c4 foreign key (status_id) references status(id) on delete cascade );'
        # 光标实例化
        cs = conn.cursor()
        # 向MySQL发送sql语句 但不执行
        cs.execute(sql_status)
        cs.execute(sql_category)
        cs.execute(sql_customer)
        cs.execute(sql_salesstaff)
        cs.execute(sql_mission)
        # 写入数据库
        conn.commit()
        cs.close()
        print('表格创建成功')
    else:
        raise TypeError('conn无效')


# 读取excel数据导入MySQL数据库
def data_input(conn):
    # salesstaff数据读取
    df_salesstaff = pd.read_excel(
        '{}/数据/销售人员.xlsx'.format(py_dir),
        sheet_name='销售信息'
    )
    df_salesstaff['name'] = df_salesstaff['name'].apply(lambda x: x.strip())
    # salesstaff数据导入
    df_salesstaff.to_sql(
        'salesstaff',
        conn,
        index=False,
        if_exists='append',
    )
    print('salesstaff表数据导入成功')
    # category数据读取
    category = pd.ExcelFile('{}/数据/顾客信息.xlsx'.format(py_dir)).sheet_names
    df_category = pd.DataFrame(np.array(category), columns=['content'])
    # category数据导入
    df_category.to_sql(
        'category',
        conn,
        index=False,
        if_exists='append',
    )
    print('category表数据导入成功')
    # customer数据读取
    df_customer_dict = pd.read_excel('{}/数据/顾客信息.xlsx'.format(py_dir), sheet_name=None)
    for key in df_customer_dict.keys():
        df_customer_dict[key]['种类'] = key
    df_customer = pd.concat([df_customer_dict[key] for key in df_customer_dict.keys()], axis=0)
    df_customer.columns = ['tel', 'category']
    # category映射
    df_category = pd.read_sql(
        'select id,content from category',
        conn,
    )
    df_category.index = df_category['content'].values
    category_id_dict = df_category.to_dict()['id']
    df_customer['category'] = df_customer['category'].apply(lambda x: category_id_dict[x])
    df_customer.columns = ['tel', 'category_id']
    # customer数据导入
    df_customer.to_sql(
        'customer',
        conn,
        index=False,
        if_exists='append',
    )
    print('customer表数据导入成功')
    # status和mission数据读取
    df_mission_dict = pd.read_excel('{}/数据/2020-06月销售任务清单.xlsx'.format(py_dir), sheet_name=None)
    for key in df_mission_dict.keys():
        df_mission_dict[key]['createDate'] = key
    df_mission = pd.concat([df_mission_dict[key] for key in df_mission_dict.keys()], axis=0)
    df_mission.columns = ['tel', 'salesstaff_name', 'status', 'createDate']
    list_status = df_mission['status'].unique().tolist()
    df_status = pd.DataFrame(np.array(list_status), columns=['content'])
    # status数据导入
    df_status.to_sql(
        'status',
        conn,
        index=False,
        if_exists='append',
    )
    print('status表数据导入成功')
    # tel映射
    df_tel = pd.read_sql(
        'select id,tel from customer',
        conn,
    )
    df_tel.index = df_tel['tel'].values
    tel_dict = df_tel.to_dict()['id']
    df_mission['tel'] = df_mission['tel'].apply(lambda x: tel_dict[x])
    # salesstaff映射
    df_salesstaff_id = pd.read_sql(
        'select id,name from salesstaff',
        conn,
    )
    df_salesstaff_id.index = df_salesstaff_id['name'].values
    salesstaff_id_dict = df_salesstaff_id.to_dict()['id']
    df_mission['salesstaff_name'] = df_mission['salesstaff_name'].apply(lambda x: salesstaff_id_dict[x])
    # status映射
    df_status = pd.read_sql(
        'select id,content from status',
        conn,
    )
    df_status.index = df_status['content'].values
    status_id_dict = df_status.to_dict()['id']
    df_mission['status'] = df_mission['status'].apply(lambda x: status_id_dict[x])
    df_mission.columns = ['customer_id', 'salesstaff_id', 'status_id', 'createDate']
    # mission数据导入
    df_mission.to_sql(
        'mission',
        conn,
        index=False,
        if_exists='append',
    )
    print('mission表数据导入成功')


if __name__ == '__main__':
    # python连接
    conn_py = connect_method(method='python')
    # pandas连接
    conn_pd = connect_method(method='pandas')
    print('创建表格...')
    creat_table(conn=conn_py)
    print('导入数据...')
    data_input(conn=conn_pd)



